<?php
namespace Application\Model;

use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Predicate;
use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;
class CitiesTable
{
    protected $tableGateway;
	protected $select;
    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
		$this->select = new Select();
    }	
	 public function fetchAll()
    {		         
         $resultSet = $this->tableGateway->select();
		 return $resultSet;	        
    }
	public function addCity($cityname,$countryid,$stateid,$cityid='')
    {
		$data = array(				
			'city_name' 		=> ucfirst($cityname),  		
			'country_id'  		=> $countryid,
			'state_id'  		=> $stateid,
			'status' 			=>	0, 				
		);
		if($cityid!=''){
			$resultSet=$this->tableGateway->update($data, array('city_id' => $cityid));
		}else{
			$resultSet =$this->tableGateway->insert($data);
		}
		return $resultSet;
    }
	public function getCity(){
		
		$select = $this->tableGateway->getSql()->select()
				->join('country', 'cities.country_id=country.country_id',array('*'),'left')
				 ->where('cities.status="1"');
		$resultSet = $this->tableGateway->selectWith($select);				
		$resultSet->buffer();
		return $resultSet;
    }
	public function getCityId($cityname){
		
		$select = $this->tableGateway->getSql()->select()
				->where(array('city_name' => $cityname));				
		$resultSet = $this->tableGateway->selectWith($select);
		return $resultSet;
    }
	public function getcitydetails($countryid,$cityname){
		
		$select = $this->tableGateway->getSql()->select()
				->where(array('city_name' => $cityname,'country_id' => $countryid));
		$resultSet = $this->tableGateway->selectWith($select);
		return $resultSet;
    }
	public function getcountrycityid($countryid,$cityname){
		$results=$this->getcitydetails($countryid,$cityname);
		if(count($results))
		{
			foreach($results as $citydetails)
			{
				return $citydetails->city_id;
			}
		}
		else
		{
			$data=array(
			'city_name'			    =>ucfirst($cityname),
			'country_id'  			=>$countryid,
			'state_id'				=>' ',
			'status'  			    =>'1',
		);	
		$this->tableGateway->insert($data);
		return $this->tableGateway->lastInsertValue;
		}
	}
	public function getcityoncountry($coutryid){
		$select = $this->tableGateway->getSql()->select()
				->where('country_id="'.$coutryid.'"');
		$resultSet = $this->tableGateway->selectWith($select);		
		return $resultSet;
	}
	public function getAllCountCities($data='')
	{
		$select = $this->tableGateway->getSql()->select();
		if(isset($data['sSearch'])){
		if($data['sSearch']!='0' && $data['sSearch']!='')
		{
		$select->join('country', 'cities.country_id=country.country_id',array('name','country_id','cuStatus'=>'status'),'left');
		
		// Search
		
				$select->where(array(
					new Predicate\PredicateSet(
						array(
						new Predicate\Like('city_name', '%'.$data['sSearch'].'%'),
						),
							Predicate\PredicateSet::COMBINED_BY_OR
					),
				));
			}
		}
		$resultSet = $this->tableGateway->selectWith($select);		
		return $resultSet->count();
	}
	public function getcityGrid($searchkey,$aColumns,$data,$paginated=false){
		
		$select = $this->tableGateway->getSql()->select();
		$select->join('country', 'cities.country_id=country.country_id',array('name','country_id','cuStatus'=>'status'),'left');
		
		// Search
		if(isset($data['sSearch'])){
			if($data['sSearch']!='0' && $data['sSearch']!='')
			{
				$select->where(array(
					new Predicate\PredicateSet(
						array(
						new Predicate\Like('city_name', '%'.$data['sSearch'].'%'),
						),
							Predicate\PredicateSet::COMBINED_BY_OR
					),
				));
			}
		}
		// End
		
		// Sort Order
		if(isset($data['iSortCol_0']))
		{
			$sOrder="";
			for($i=0;$i<intval($data['iSortingCols']);$i++)
			{
				if($data['bSortable_'.intval($data['iSortCol_'.$i])] == "true")
				{
					$sOrder.=$aColumns[intval($data['iSortCol_'.$i])]." ".$data['sSortDir_'.$i].",";
				}
			}
			$sOrder = substr_replace($sOrder,"", -1);
		}
		//if($sOrder!=''){
			//$select->order($sOrder);
		//}
		// End
		
		// Pagination
		if(isset($data['iDisplayStart']) && $data['iDisplayLength'] != '-1')
		{
			$start=(int)$data['iDisplayStart'];
			$currentPage = (int)$data['iDisplayLength'];
			$resultsPerPage = (int) $currentPage;
			//echo "<pre>";print_r($data['iDisplayLength']);exit;
			$select->order('city_id DESC')->limit($currentPage)->offset($start);
		}
		// End
		
		$resultSet = $this->tableGateway->selectWith($select);
		
		return $resultSet;
	}
	public function citydetails($cityid){	
		$select = $this->tableGateway->getSql()->select()
				->where('city_id="'.$cityid.'"');
		$resultSet = $this->tableGateway->selectWith($select);		
		return $resultSet;
	}
	public function changeStatus($catIds,$value)
	{
		$data = array(
			'status'       =>$value,
		);
		$updateStatus=$this->tableGateway->update($data,array('(city_id IN ('.$catIds.'))'));
		return 	$updateStatus;			
	}
	public function checkonCity($cityname){	
		$select = $this->tableGateway->getSql()->select()
				->where('city_name="'.$cityname.'"');
		$resultSet = $this->tableGateway->selectWith($select);		
		return $resultSet->count();
	}
	public function deleteCity($ids){		
		$this->tableGateway->delete(array('(city_id IN ('.$ids.'))'));			
		return $this->tableGateway->lastInsertValue;	
	}
}